/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.googlecode.datawander;

import com.googlecode.datawander.connectors.MyRelationalDataBaseException;
import com.googlecode.datawander.connectors.OracleConnector;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.log4j.Logger;

/**
 *
 * @author Marcin Stachniuk
 */
public class CreateOracleDatabase {

    private static final Logger logger = Logger.getLogger(CreateOracleDatabase.class);

    public void dropTableWithAll() throws MyRelationalDataBaseException {
            logger.trace("dropTableWithAll()");

        PreparedStatement pstmt = null;
        Connection conn = null;
        conn = OracleConnector.getConnection();
        try {
            logger.info("Usowam oracle_all_types");
            pstmt = conn.prepareStatement("drop table oracle_all_types");
            pstmt.executeUpdate();
        } catch(Exception e) {
            // ignore
        }

        try {
            logger.info("Usowam varray_type");
            pstmt = conn.prepareStatement("drop type varray_type");
            pstmt.executeUpdate();
        } catch(Exception e) {
            // ignore
        }

        try {
            logger.info("Usowam oracle_object");
            pstmt = conn.prepareStatement("drop type oracle_object");
            pstmt.executeUpdate();
        } catch(Exception e) {
            // ignore
        }

        logger.info("Zakonczono usowanie bazy");
    }

    public void createTableWithAll() throws MyRelationalDataBaseException {
        logger.trace("createTableWithAll()");
        PreparedStatement pstmt = null;
        Connection conn = null;
        StringBuilder sqlBuilder = new StringBuilder();
        conn = OracleConnector.getConnection();


        try {
            logger.info("Tworzenie varray_type");
            sqlBuilder = new StringBuilder("CREATE TYPE varray_type is VARRAY(5) OF VARCHAR(10)");
            pstmt = conn.prepareStatement(sqlBuilder.toString());
            pstmt.executeUpdate();
			logger.info("Wykonano zapytanie: "+pstmt.toString());

            // Create an OBJECT type
            logger.info("Tworzenie oracle_object");
            sqlBuilder = new StringBuilder("CREATE TYPE oracle_object is OBJECT(column_string VARCHAR(128), column_integer INTEGER)");
            pstmt = conn.prepareStatement(sqlBuilder.toString());
            pstmt.executeUpdate();
			logger.info("Wykonano: "+pstmt.toString());


            sqlBuilder = new StringBuilder("CREATE TABLE oracle_all_types(");
            //                    Column Name          Oracle Type              Java Type
            sqlBuilder.append("column_short           SMALLINT, ");          // short
            sqlBuilder.append("column_int             INTEGER, ");           // int
            sqlBuilder.append("column_float           REAL, ");              // float; can also be NUMBER
            sqlBuilder.append("column_double          DOUBLE PRECISION, ");  // double; can also be FLOAT or NUMBER
            //sqlBuilder.append("column_bigdecimal      DECIMAL(13,0), ");     // BigDecimal
            sqlBuilder.append("column_string          VARCHAR2(254), ");     // String; can also be CHAR(n)
            //sqlBuilder.append("column_characterstream LONG, ");              // CharacterStream or AsciiStream
            sqlBuilder.append("column_bytes           RAW(2000), ");         // byte[]; can also be LONG RAW(n)
            //sqlBuilder.append("column_binarystream    RAW(2000), ");         // BinaryStream; can also be LONG RAW(n)
            sqlBuilder.append("column_timestamp       DATE, ");              // Timestamp
            sqlBuilder.append("column_clob            CLOB, ");              // Clob
            sqlBuilder.append("column_blob            BLOB, ");              // Blob; can also be BFILE
            sqlBuilder.append("column_bfile           BFILE, ");             // oracle.sql.BFILE
            sqlBuilder.append("column_array           varray_type, ");       // oracle.sql.ARRAY
            sqlBuilder.append("column_object          oracle_object)");      // oracle.sql.OBJECT

            pstmt.executeUpdate(sqlBuilder.toString());

            //TODO: wypelnic danymi:
            // insert into ORACLE_ALL_TYPES (COLUMN_SHORT, COLUMN_INT, COLUMN_FLOAT, COLUMN_DOUBLE) values (6, 11, 21.5, 31.12)
            sqlBuilder = new StringBuilder("insert into ORACLE_ALL_TYPES (COLUMN_SHORT, COLUMN_INT, COLUMN_FLOAT, COLUMN_DOUBLE) values (6, 11, 21.5, 31.12)");

            pstmt.executeUpdate(sqlBuilder.toString());
            pstmt.executeUpdate(sqlBuilder.toString());
            
        } catch (SQLException e) {
            String err = e.getMessage();
            if(e.getMessage().equals("ORA-00955: name is already used by an existing object\n")) {
                    logger.error("Dany typ / tabela isnieje juz w bazie danych. \nZapytanie: "+sqlBuilder.toString(), e);
                    throw new MyRelationalDataBaseException("Dany typ / tabela isnieje juz w bazie danych");
            } else {
                logger.error(e, e);
                throw new MyRelationalDataBaseException(e.getMessage());
            }

        } catch (Exception e) {
            logger.error(e, e);
        }
    }

    public static  int getNumberOfRecordsForTable(String tableName) throws Exception {
        Connection conn = OracleConnector.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select COUNT(*) how from \"" + tableName + "\"");
        ResultSet result = stmt.executeQuery();
        result.next();
        return result.getInt("how");
    }
}
